/*
 * GTFSDatabase.java
 * 
 * Created by demory on Nov 11, 2009, 3:40:33 PM
 * 
 * Copyright 2008 David D. Emory
 * 
 * This file is part of Five Points. See <http://www.fpdev.org> for
 * additional information regarding the project.
 * 
 * Five Points is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * Five Points is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with Five Points.  If not, see <http://www.gnu.org/licenses/>.
 */

package org.fpdev.core.transit.gtfs;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.fpdev.core.DataPackage;

/**
 *
 * @author demory
 */
public class GTFSDatabase {

  private DataPackage dataPkg_;

  private String user_, pw_;
  protected Connection conn_;

  public GTFSDatabase(DataPackage dp) {
    dataPkg_ = dp;
    user_ = "fproot";
    pw_ = "cypress";
    try {
      connect();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  public void connect() throws SQLException {
    String url = "jdbc:mysql://localhost/";

    System.out.println("user="+user_);
    System.out.println("pw="+pw_);
    try {

      Class.forName("com.mysql.jdbc.Driver").newInstance();
      System.out.println("Loaded the appropriate driver.");

      String dbName = dataPkg_.getName() + "_gtfs";
      conn_ = DriverManager.getConnection(url + dbName + "?autoReconnect=true", user_, pw_);
      System.out.println("Connected to existing MySQL database " +dbName);
      //initialized_ = true;
    } catch (Exception e) {
      //initialized_ = createDB(url);
      e.printStackTrace();
    }
  }

  public boolean tableExists(String table) {
    boolean result = false;
    try {
      ResultSet rs = conn_.getMetaData().getTables(null, null, null, null);
      while(rs.next()) {
        String name = rs.getString("TABLE_NAME");
        //System.out.println(name);
        if(name.toLowerCase().equals(table.toLowerCase())) result = true;
      }

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
    return result;
  }

  public void createTable(String name, String[] cols) {
    try {
      Statement s = conn_.createStatement();
      String query = "create table "+name+"(id int primary key auto_increment";
      for(String col : cols) {
        query +=  ", "+col+" varchar(128)";
      }
      query += ")";

      s.execute(query);
      s.close();
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void addRow(String table, List<String> values) {
    try {
      Statement s = conn_.createStatement();
      String query = "INSERT INTO "+table+" VALUES (NULL";
      for(String val : values) {
        query +=  ", '"+val+"'";
      }
      query += ")";

      s.executeUpdate(query);
      s.close();
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public List<String[]> getData(String table) {
    return getData(table, "");
  }

  public List<String[]> getData(String table, String condition) {
    List<String[]> data = new LinkedList<String[]>();
    try {
      Statement s = conn_.createStatement();
      ResultSet rs = s.executeQuery("SELECT * FROM " + table + (condition.length() > 0 ? " WHERE "+condition : ""));
      while(rs.next()) {
        String[] row = new String[rs.getMetaData().getColumnCount()];
        for(int i =0; i< rs.getMetaData().getColumnCount(); i++)
          row[i] = rs.getString(i+1);
        data.add(row);
      }
      rs.close();
      s.close();
    } catch (Exception ex) {
      ex.printStackTrace();
    }
    return data;
  }

  public Map<String, Integer> getColumnNames(String table) {
    Map<String, Integer> lookup = new HashMap<String, Integer>();
    try {
      Statement s = conn_.createStatement();
      ResultSet rs = s.executeQuery("SELECT * FROM " + table);
      //rs.next();
      //String[] row = new String[rs.getMetaData().getColumnCount()];
      for(int i =0; i< rs.getMetaData().getColumnCount(); i++) {
        lookup.put(rs.getMetaData().getColumnName(i+1), i);
        System.out.println(rs.getMetaData().getColumnName(i+1)+" "+(i+1));
      }
      //  data.add(row);
      //}
      rs.close();
      s.close();
    } catch (Exception ex) {
      ex.printStackTrace();
    }
    return lookup;
  }

  public void close() {
    try {
      System.out.println("Closing MySQL BaseDB..");
      conn_.close();
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

}
